using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using NopSolutions.NopCommerce.BusinessLogic.Data;
using NopSolutions.NopCommerce.BusinessLogic.Products.Specs;

namespace NopSolutions.NopCommerce.LuceneHelper
{
    public class DatabaseHelper
    {
        #region Options

        public static IList<SpecificationAttributeOption> GetAllMappedOptions()
        {
            var context = ObjectContextHelper.CurrentObjectContext;
            var distinctMappedOptions =
                context.ProductSpecificationAttributes.Where(x => x.AllowFiltering).Select(
                    x => x.SpecificationAttributeOptionId).Distinct();

            var tasks = distinctMappedOptions.Join(context.SpecificationAttributeOptions,
                                             optionId => optionId,
                                             option => option.SpecificationAttributeOptionId,
                                             (optionId, option) => option);
            return tasks.ToList();
        }

        #region Categories

        public static List<int> GetCategoriesForOption(int specificationOptionId)
        {
            return GetResults(specificationOptionId, GetCategoryOptionsSql(specificationOptionId));
        }

        private static string GetCategoryOptionsSql(int specificationOptionId)
        {
            return "SELECT DISTINCT " +
                        "c.CategoryId AS ival " +
                    "FROM Nop_Category c " +
                        "INNER JOIN Nop_Product_Category_Mapping cm ON " +
                            "cm.CategoryID = c.CategoryID " +
                        "INNER JOIN Nop_Product p ON " +
                            "p.ProductId = cm.ProductID " +
                        "INNER JOIN Nop_ProductVariant pv ON " +
                            "pv.ProductID = p.ProductId " +
                        "INNER JOIN Nop_Product_SpecificationAttribute_Mapping som ON " +
                            "som.ProductID = p.ProductId " +
                        "INNER JOIN Nop_SpecificationAttributeOption so ON " +
                            "so.SpecificationAttributeOptionID = som.SpecificationAttributeOptionID " +
                    "WHERE " +
                            "so.SpecificationAttributeOptionID = " + specificationOptionId.ToString() + " " +
                        "AND " +
                            "p.Published = 1 " +
                        "AND " +
                            "pv.Published = 1 " +
                        "AND " +
                            "p.Deleted=0 " +
                        "AND " +
                            "som.AllowFiltering = 1 " +
                        "AND " +
                            "getutcdate() between isnull(pv.AvailableStartDateTime, '1/1/1900') and isnull(pv.AvailableEndDateTime, '1/1/2999')";
        }

        #endregion

        #region Manufacturers

        public static List<int> GetManufacturersForOption(int specificationOptionId)
        {
            return GetResults(specificationOptionId, GetManufacturerOptionsSql(specificationOptionId));
        }

        private static string GetManufacturerOptionsSql(int specificationOptionId)
        {
            return "SELECT DISTINCT " +
                        "m.ManufacturerId as ival " +
                    "FROM Nop_Manufacturer m " +
                        "INNER JOIN Nop_Product_Manufacturer_Mapping mm ON " +
                            "mm.ManufacturerID = m.ManufacturerID " +
                        "INNER JOIN Nop_Product p ON " +
                            "p.ProductId = mm.ProductID " +
                        "INNER JOIN Nop_ProductVariant pv ON " +
                            "pv.ProductID = p.ProductId " +
                        "INNER JOIN Nop_Product_SpecificationAttribute_Mapping som ON " +
                            "som.ProductID = p.ProductId " +
                        "INNER JOIN Nop_SpecificationAttributeOption so ON " +
                            "so.SpecificationAttributeOptionID = som.SpecificationAttributeOptionID " +
                    "WHERE " +
                            "so.SpecificationAttributeOptionID = " + specificationOptionId.ToString() + " " +
                        "AND " +
                            "p.Published = 1 " +
                        "AND " +
                            "pv.Published = 1 " +
                        "AND " +
                            "p.Deleted=0 " +
                        "AND " +
                            "som.AllowFiltering = 1 " +
                        "AND " +
                            "getutcdate() between isnull(pv.AvailableStartDateTime, '1/1/1900') and isnull(pv.AvailableEndDateTime, '1/1/2999')";
        }

        #endregion

        #endregion

        #region Attributes

        public static IList<SpecificationAttribute> GetAllMappedAttributes()
        {
            var context = ObjectContextHelper.CurrentObjectContext;
            var distinctMappedAttributes =
                context.ProductSpecificationAttributes.Where(x => x.AllowFiltering).Join(
                    context.SpecificationAttributeOptions,
                    psa => psa.SpecificationAttributeOptionId,
                    sao => sao.SpecificationAttributeOptionId,
                    (psa, sao) => sao).Join(context.SpecificationAttributes,
                    sao => sao.SpecificationAttributeId,
                    sa => sa.SpecificationAttributeId,
                    (sao, sa) => sa).Select(x => x.SpecificationAttributeId).Distinct();

            var tasks = distinctMappedAttributes.Join(context.SpecificationAttributes,
                                             attributeId => attributeId,
                                             attribute => attribute.SpecificationAttributeId,
                                             (attributeId, attribute) => attribute);
            return tasks.ToList();
        }

        #region Categories

        public static List<int> GetCategoriesForAttribute(int specificationId)
        {
            return GetResults(specificationId, GetCategoryAttributesSql(specificationId));
        }

        private static string GetCategoryAttributesSql(int specificationId)
        {
            return "SELECT DISTINCT " +
                        "c.CategoryId AS ival " +
                    "FROM Nop_Category c " +
                        "INNER JOIN Nop_Product_Category_Mapping cm ON " +
                            "cm.CategoryID = c.CategoryID " +
                        "INNER JOIN Nop_Product p ON " +
                            "p.ProductId = cm.ProductID " +
                        "INNER JOIN Nop_ProductVariant pv ON " +
                            "pv.ProductID = p.ProductId " +
                        "INNER JOIN Nop_Product_SpecificationAttribute_Mapping som ON " +
                            "som.ProductID = p.ProductId " +
                        "INNER JOIN Nop_SpecificationAttributeOption so ON " +
                            "so.SpecificationAttributeOptionID = som.SpecificationAttributeOptionID " +
                    "WHERE " +
                            "so.SpecificationAttributeID = " + specificationId.ToString() + " " +
                        "AND " +
                            "p.Published = 1 " +
                        "AND " +
                            "pv.Published = 1 " +
                        "AND " +
                            "p.Deleted=0 " +
                        "AND " +
                            "som.AllowFiltering = 1 " +
                        "AND " +
                            "getutcdate() between isnull(pv.AvailableStartDateTime, '1/1/1900') and isnull(pv.AvailableEndDateTime, '1/1/2999')";
        }

        #endregion

        #region Manufacturers

        public static List<int> GetManufacturersForAttribute(int specificationId)
        {
            return GetResults(specificationId, GetManufacturerAttributesSql(specificationId));
        }

        private static string GetManufacturerAttributesSql(int specificationId)
        {
            return "SELECT DISTINCT " +
                        "m.ManufacturerId as ival " +
                    "FROM Nop_Manufacturer m " +
                        "INNER JOIN Nop_Product_Manufacturer_Mapping mm ON " +
                            "mm.ManufacturerID = m.ManufacturerID " +
                        "INNER JOIN Nop_Product p ON " +
                            "p.ProductId = mm.ProductID " +
                        "INNER JOIN Nop_ProductVariant pv ON " +
                            "pv.ProductID = p.ProductId " +
                        "INNER JOIN Nop_Product_SpecificationAttribute_Mapping som ON " +
                            "som.ProductID = p.ProductId " +
                        "INNER JOIN Nop_SpecificationAttributeOption so ON " +
                            "so.SpecificationAttributeOptionID = som.SpecificationAttributeOptionID " +
                    "WHERE " +
                            "so.SpecificationAttributeID = " + specificationId.ToString() + " " +
                        "AND " +
                            "p.Published = 1 " +
                        "AND " +
                            "pv.Published = 1 " +
                        "AND " +
                            "p.Deleted=0 " +
                        "AND " +
                            "som.AllowFiltering = 1 " +
                        "AND " +
                            "getutcdate() between isnull(pv.AvailableStartDateTime, '1/1/1900') and isnull(pv.AvailableEndDateTime, '1/1/2999')";
        }

        #endregion

        #endregion

        #region Heleprs

        private static List<int> GetResults(int specificationOptionId, string sql)
        {
            var context = NopSolutions.NopCommerce.BusinessLogic.Data.ObjectContextHelper.CurrentObjectContext;
            var results = new List<int>();
            var entityConnection = (System.Data.EntityClient.EntityConnection)context.Connection;
            DbConnection conn = entityConnection.StoreConnection;

            ConnectionState initialState = conn.State;
            try
            {
                if (initialState != ConnectionState.Open)
                    conn.Open();  // open connection if not already open
                using (DbCommand cmd = conn.CreateCommand())
                {
                    var optionId = cmd.CreateParameter();
                    optionId.DbType = DbType.Int32;
                    optionId.Value = specificationOptionId;
                    cmd.Parameters.Add(optionId);
                    cmd.CommandText = sql;

                    using (IDataReader dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        while (dataReader.Read())
                        {
                            results.Add(GetInt(dataReader, "ival"));
                        }
                    }
                }
            }
            finally
            {
                if (initialState != ConnectionState.Open)
                    conn.Close(); // only close connection if not initially open
            }
            return results;
        }

        private static int GetInt(IDataReader rdr, string columnName)
        {
            int index = rdr.GetOrdinal(columnName);
            if (rdr.IsDBNull(index))
            {
                return 0;
            }
            return (int)rdr[index];
        }

        #endregion

        #region Nop helpers

        public static void MakeAttributeNotSortable(int specificationId)
        {
            var context = ObjectContextHelper.CurrentObjectContext;
            var query = (from psa in context.ProductSpecificationAttributes
                         join so in context.SpecificationAttributeOptions on psa.SpecificationAttributeOptionId equals
                             so.SpecificationAttributeOptionId
                         where so.SpecificationAttributeId == specificationId
                         select psa).ToList();
            foreach (var productSpecificationOption in
                query.Where(productSpecificationOption => productSpecificationOption.AllowFiltering))
            {
                if (!context.IsAttached(productSpecificationOption))
                    context.ProductSpecificationAttributes.Attach(productSpecificationOption);
                productSpecificationOption.AllowFiltering = false;
                context.SaveChanges();
            }
        }

        #endregion

    }
}